CREATE OPERATOR
CREATE OPERATOR — Define a new operator
Synopsis
CREATE OPERATOR name (
{FUNCTION|PROCEDURE} = function_name
[, LEFTARG = left_type ] [, RIGHTARG = right_type ]
[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
[, RESTRICT = res_proc ] [, JOIN = join_proc ]
[, HASHES ] [, MERGES ]
)
Description
CREATE OPERATOR defines a new operator named name. The user who defines the operator becomes its owner. If a schema name is given, the operator will be created in the specified schema. Otherwise, it will be created in the current schema.
The operator name is a sequence of up to NAMEDATALEN-1 (default 63) characters from the following set:
-
-
- / < > = ~ ! @ #% ^ & | ` ?
-
There are some restrictions on the choice of name:
• -- and /* cannot appear in an operator name, because they will be interpreted as the start of a comment.
• A multi-character operator name cannot end with + or -, unless the name also contains at least one of the following characters:
~ ! @ #% ^ & | ` ?
For example, @- is an allowed operator name, but *- is not. This restriction allows the system to parse SQL-compatible commands without requiring spaces between tokens.
• Using => as an operator name is deprecated. It may be disabled in future releases.
On input, != is mapped to <>, so these two names are always equivalent.
At least one of LEFTARG and RIGHTARG must be defined. For a binary operator, both must be defined. For a right-unary operator, only LEFTARG should be defined. For a left-unary operator, only RIGHTARG should be defined.
The function_name function must have been previously defined with CREATE FUNCTION and must be defined to accept the correct number of arguments of the specified types.
In the syntax of CREATE OPERATOR, the keywords FUNCTION and PROCEDURE are equivalent, but in either case the referenced function must be a function, not a procedure. The use of the keyword PROCEDURE here is for historical reasons and is now deprecated.
Other clauses specify optional operator optimization clauses.
To create an operator, you must have USAGE privilege on the argument types and return type, as well as EXECUTE privilege on the underlying function. If a commutator or negator operator is specified, you must own these operators.
Parameters
name
The name of the operator to be defined. See above for allowed characters. The name can be schema-qualified, for example CREATE OPERATOR myschema.+ (...). If not schema-qualified, the operator will be created in the current schema.
If two operators in the same schema operate on different data types, they can have the same name. This is called overloading.
function_name
The function used to implement this operator.
left_type
The data type of the left operand (if any) of this operator. Omitting this option indicates a left-unary operator.
right_type
The data type of the right operand (if any) of this operator. Omitting this option indicates a right-unary operator.
com_op
The commutator of this operator.
neg_op
The negator of this operator.
res_proc
The restriction selectivity estimator function for this operator.
join_proc
The join selectivity estimator function for this operator.
HASHES
Indicates that this operator can support hash joins.
MERGES
Indicates that this operator can support merge joins.
To specify a schema-qualified operator name in com_op or other optional parameters, use the OPERATOR() syntax, For example:
COMMUTATOR = OPERATOR(myschema.===) ,
Notes
It is not possible to specify the lexical precedence of an operator in CREATE OPERATOR, because the parser's precedence behavior is hard-coded.
The deprecated options SORT1, SORT2, LTCMP, and GTCMP were previously used to specify the names of sort operators associated with operators supporting merge joins. They are no longer needed because the information about related operators can be found in B-tree operator families. If one of these options is given, it will be ignored (except that it implicitly sets MERGES to true).
Use DROP OPERATOR to remove user-defined operators from the database. Use ALTER OPERATOR to modify operators in the database.
Examples
The following command defines a new operator for the data type box — area equality:
CREATE OPERATOR === (
LEFTARG = box,
RIGHTARG = box,
FUNCTION = area_equal_function,
COMMUTATOR = ===,
NEGATOR = !==,
RESTRICT = area_restriction_function,
JOIN = area_join_function,
HASHES, MERGES
);